import pandas as pd
from datetime import datetime,timedelta
from sqlalchemy import create_engine,types,text
from qmt_tool.doris_tool import DbTool
##从数据库中获取数据
class DBQuote():
    def __init__(self):
        self.dbtool=DbTool()
        self.dbengine= self.dbtool.get_dbengine_quant()
        self.dbengine_result= self.dbtool.get_dbengine_quant_result()

    def get_dbengine(self):
        return self.dbengine

    def get_result_dbengine(self):
        return self.dbengine_result
    
    def save_to_db_replace(self,df,table_name,dbengine=None):
        if dbengine is None:
            dbengine=self.dbtool.get_dbengine_quant()
        else:
            dbengine=dbengine
        self.dbtool.save_to_mysql_replace(df, self.dbengine, table_name)
    
    def save_to_db_append(self,df,table_name,dbengine=""):
        if dbengine == "":
            dbengine=self.dbtool.get_dbengine_quant()
        else:
            dbengine=dbengine
        self.dbtool.save_to_mysql_append(df, dbengine, table_name)

    def is_trade_day_today(self):
        today = datetime.now().strftime('%Y%m%d')
        sql_comm = "select * from trade_cal_ak_source where trade_date = :today "
        dbengine = self.dbengine
        data_df = pd.read_sql(text(sql_comm), dbengine, params={"today": today})
        pre_trade_date = None
        is_today_open = None
        is_today_open = False

        if data_df.shape[0] != 0:

            is_today_open = data_df.is_open.values[0]
            pre_trade_date = data_df.pretrade_date.values[0]
        else:
            for itx in range(1, 10):
                pre = (datetime.now() - timedelta(days=itx)).strftime('%Y%m%d')
                sql_comm = "select * from trade_cal_ak_source where trade_date = :pre "
                data_df2 = pd.read_sql(text(sql_comm), dbengine, params={"pre": pre})
                if data_df2.shape[0] != 0:
                    pre_trade_date = data_df2.trade_date.values[0]
                    is_today_open = '0'

                    break

        if str(is_today_open) == "1":
            return today, True, pre_trade_date
        else:
            return today, False, pre_trade_date

    def get_date_before_today(self,N):
        N_days_ago = datetime.now() - timedelta(days=N)
        return N_days_ago.strftime('%Y-%m-%d')

# 自由流通股
    def get_free_share_all_fromdb(self):
        today,rlt,pre_trade_date=self.is_trade_day_today()
        sql_comm="""SELECT basic.ts_code,trade_date,list.symbol,list.name,list.industry,basic.pe_ttm,float_share*10000 as float_share,free_share*10000 as free_share,circ_mv FROM `stock_basic_daily`  basic
left join `stock_list` list 
on  list.ts_code=basic.ts_code 
where basic.trade_date= :pre_trade_date """
        dbengine = self.dbengine
        data_df = pd.read_sql(text(sql_comm), dbengine, params={"pre_trade_date": pre_trade_date})
        return  data_df

    # 实时行情数据 从东财获取 数据长度很长,
    def get_code_all_daily_qfq_em_fromdb(self,symbol):
        dbengine = self.dbengine
        sql_comm = "select * from ak_stock_daily_qfq_em where symbol = :symbol   order by trade_date asc"
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
            df.index=df['trade_date']
            df['date'] = df['trade_date']
            pd.to_datetime(df["date"])
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)

    def get_fund_all_daily_em_fromdb(self, symbol):
            dbengine = self.dbengine
            sql_comm = "select * from ak_fund_daily_em where symbol = :symbol order by trade_date asc"
            end_date = datetime.now().strftime('%Y%m%d')
            try:
                df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
                df.index = df['trade_date']
                df['date'] = df['trade_date']
                return df, end_date
            except Exception as e:
                print('读取数据出错', e)
                return exit(1)

    # 实时行情数据 从东财获取 数据长度很长,
    def get_code_all_weekly_qfq_em_fromdb(self,symbol):
        dbengine = self.dbengine
        sql_comm = "select * from ak_stock_weekly_qfq_em where symbol = :symbol order by trade_date asc"
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
            df.index=df['trade_date']
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)
    def get_code_all_monthly_qfq_em_fromdb(self,symbol):
        dbengine = self.dbengine
        sql_comm = "select * from ak_stock_monthly_qfq_em where symbol = :symbol order by trade_date asc"
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)
            return exit(1)

    # 实时行情数据 从东财获取 数据长度很长,
    def get_code_someday_qfq_em_fromdb(self,symbol,start_date='',end_date=''):
        if start_date=='':
            start_date='20220202'
        else:
            start_date=start_date
        if end_date=='':
            end_date=datetime.now().strftime('%Y%m%d')
        else:
            end_date=end_date
        dbengine = self.dbengine
        sql_comm = "select * from ak_stock_daily_qfq_em where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index = df['trade_date']
            df['date'] = pd.to_datetime(df['trade_date'])
            # pd.to_datetime(df["date"])
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)

    def get_jhjj_pool_fromdb(self,is_pre_tradedate=True,and_prams=";"):
        dbengine = self.dbengine
        andpramas=and_prams
        today=datetime.now().strftime('%Y%m%d')
        today,rlt,pre_trade_date=self.is_trade_day_today()

        if is_pre_tradedate==True:
        # sql_comm = "select * from ak_jhjj where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
            sql_comm = "select * from ak_stock_jhjj_snapshot where trade_date = :pre_trade_date " + andpramas

            try:
                df = pd.read_sql(text(sql_comm), dbengine, params={"pre_trade_date": pre_trade_date})
                stock_pool = df['symbol'].tolist()
                return stock_pool,df
            except Exception as e:
                print('读取数据出错', e)
        else:
            sql_comm = "select * from ak_stock_jhjj_snapshot where trade_date=curdate()" + andpramas

            try:
                df = pd.read_sql(text(sql_comm), dbengine)
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
            except Exception as e:
                print('读取数据出错', e)


    def get_fund_someday_em_fromdb(self,symbol,start_date='',end_date=''):
        if start_date=='':
            start_date='20220202'
        else:
            start_date=start_date
        if end_date=='':
            end_date=datetime.now().strftime('%Y%m%d')
        else:
            end_date=end_date
        dbengine = self.dbengine
        sql_comm = "select * from ak_fund_daily_em where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index = df['trade_date']
            df['date'] = df['trade_date']
            pd.to_datetime(df["date"])
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)
    #获取流通市值大于40亿， 去除北交所，去除ST ，房地产行业股票
    def get_default_stock_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()

        SQL_COMM="""
        select lst.symbol,lst.name,lst.industry,lst.float_share,lst.float_value 
         from quant.ak_stock_daily_qfq_em  daily ,quant.ak_stock_list lst where  lst.symbol=daily.symbol and lst.symbol  not in (select symbol from black_stock_list) and trade_date=:pre_trade_date and close >6 and lst.float_value >4000000000;
        """
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(SQL_COMM), dbengine,params={"pre_trade_date": pre_trade_date})
            stock_pool = df['symbol'].tolist()
            return stock_pool,df
        except Exception as e:
            print('读取数据出错', e)



# 获取今日 上涨股
    def get_default_stock_up_pool(self,zhangdiefu_max=6.5,zhangdiefu_min=3.5):
            today, rlt, pre_trade_date = self.is_trade_day_today()

            # SQL_COMM = f"""
            # select lst.symbol,lst.name,lst.industry,lst.float_share,lst.float_value  from quant.ak_stock_daily_qfq_em  daily ,quant.ak_stock_list lst where  lst.symbol=daily.symbol and lst.symbol  not in (select symbol from black_stock_list) and  daily.zhangdiefu >{} and daily.zhangdiefu <{} and  trade_date=:pre_trade_date and close >6 and lst.float_value >5000000000;
            # """.format(zhangdiefu_min,zhangdiefu_max)
            # 假设已经配置了SQLAlchemy的引擎dbengine
            SQL_COMM = text("""
                            select lst.symbol,lst.name,lst.industry,lst.float_share,lst.float_value
                            from quant.ak_stock_daily_qfq_em daily, quant.ak_stock_list lst
                            where lst.symbol=daily.symbol
                            and lst.symbol not in (select symbol from black_stock_list)
                            and daily.zhangdiefu > :zhangdiefu_min
                            and daily.zhangdiefu < :zhangdiefu_max
                            and trade_date = :pre_trade_date
                            and close > 6
                            and lst.float_value > 4000000000;
                        """)



            dbengine = self.dbengine

            try:
                if rlt:
                    df = pd.read_sql(SQL_COMM, dbengine, params={
                'zhangdiefu_min': zhangdiefu_min,
                'zhangdiefu_max': zhangdiefu_max,
                'pre_trade_date': today
                }
)
                    stock_pool = df['symbol'].tolist()
                    return stock_pool, df
                else:
                    df = pd.read_sql(SQL_COMM, dbengine, params={
                'zhangdiefu_min': zhangdiefu_min,
                'zhangdiefu_max': zhangdiefu_max,
                'pre_trade_date': pre_trade_date
                }
)
                    stock_pool = df['symbol'].tolist()
                    return stock_pool, df
            except Exception as e:
                print('读取数据出错', e)
            # 获取今日 上涨股

    def get_default_stock_down_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()

        SQL_COMM = """
                select lst.symbol,lst.name,lst.industry,lst.float_share,lst.float_value 
                 from quant.ak_stock_daily_qfq_em  daily ,quant.ak_stock_list lst where  lst.symbol=daily.symbol and lst.symbol  not in (select symbol from black_stock_list) and  daily.zhangdiefu <0 and  trade_date=:pre_trade_date and close >6 and lst.float_value >3000000000;
                """
        dbengine = self.dbengine

        try:
            if rlt:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": today})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
            else:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": pre_trade_date})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)

        # 最强300 标的用于，回马枪和趋势上升的选股策略，默认股票池则可以全局搜索
    def get_zuiqiang300_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()
        SQL_COMM="""         select * from ( 
			   		select daily.symbol,daily.name,lst.industry,lst.float_share,lst.float_value
             from quant.daily_zuiqiang_300 daily 
    				 left join quant.ak_stock_list lst on lst.symbol=daily.symbol
    				 where   trade_date=:pre_trade_date and hy_nm is not null )BB  where   symbol not in (select symbol from black_stock_list) and industry is not null;
						 
        """
        dbengine=self.dbengine
   
        try:
            if rlt: 
                df = pd.read_sql(text(SQL_COMM), dbengine,params={"pre_trade_date": today})
                stock_pool = df['symbol'].tolist()
                return stock_pool,df
            else:
                df = pd.read_sql(text(SQL_COMM), dbengine,params={"pre_trade_date": pre_trade_date})
                stock_pool = df['symbol'].tolist()
                return stock_pool,df
        except Exception as e:
                print('读取数据出错', e)


    def get_angle_up_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()

        SQL_COMM = """
              select symbol,name,industry,circ_mv,angle5,angle10,angle20,angle30,angle60,angle120,bias20,bias30 from
               quant_result.feature_stock_ma_angle_daily where angle20>20 and angle30>10 and angle10>30 and angle5>30 and angle120>0 
               and trade_date=:pre_trade_date ORDER BY bias30 desc
                """
        dbengine = self.dbengine

        try:
            if rlt:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": today})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
            else:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": pre_trade_date})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)
        # 获取今日 上涨股

    def get_zuiqiang300_up_pool(self):
            today, rlt, pre_trade_date = self.is_trade_day_today()
            SQL_COMM = """ select * from ( 
			   		select daily.symbol,daily.name,lst.industry,lst.float_share,lst.float_value
             from quant.daily_zuiqiang_300 daily 
    				 left join quant.ak_stock_list lst on lst.symbol=daily.symbol
    				 where daily.zhangdiefu>0 and daily.zhangdiefu<9.5 and  trade_date=:pre_trade_date and hy_nm is not null )BB  where   symbol not in (select symbol from black_stock_list) and industry is not null; 
        """

            dbengine = self.dbengine

            try:
                if rlt:
                    df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": today})
                    stock_pool = df['symbol'].tolist()
                    return stock_pool, df
                else:
                    df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": pre_trade_date})
                    stock_pool = df['symbol'].tolist()
                    return stock_pool, df
            except Exception as e:
                print('读取数据出错', e)

    def get_zhangfu_up_pool(self,zhangfu:float,xiangao:float):
        today, rlt, pre_trade_date = self.is_trade_day_today()

        SQL_COMM = """
            select lst.symbol,lst.name,lst.industry,lst.float_share,lst.float_value 
             from quant.ak_stock_daily_qfq_em  daily ,quant.ak_stock_list lst where  lst.symbol=daily.symbol and lst.symbol  not in (select symbol from black_stock_list) and  daily.zhangdiefu >:zhangfu and daily.zhangdiefu <:zhanggao and  trade_date=:pre_trade_date and close >6 and lst.float_value >3000000000;
            """
        dbengine = self.dbengine

        try:
                if rlt:
                    df = pd.read_sql(text(SQL_COMM), dbengine, params={"zhangfu":zhangfu,"zhanggao":xiangao,"pre_trade_date": today})
                    stock_pool = df['symbol'].tolist()
                    return stock_pool, df
                else:
                    df = pd.read_sql(text(SQL_COMM), dbengine,  params={"zhangfu":zhangfu,"zhanggao":xiangao,"pre_trade_date": today})
                    stock_pool = df['symbol'].tolist()
                    return stock_pool, df
        except Exception as e:
                print('读取数据出错', e)


    def get_zuiqiang300_down_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()
        SQL_COMM = """         select * from ( 
    			   		select daily.symbol,daily.name,lst.industry,lst.float_share,lst.float_value
                 from quant.daily_zuiqiang_300 daily 
        				 left join quant.ak_stock_list lst on lst.symbol=daily.symbol
        				 where daily.zhangdiefu<0 and  trade_date=:pre_trade_date and hy_nm is not null )BB  where  symbol not in (select symbol from black_stock_list) and  industry is not null;

            """

        dbengine = self.dbengine

        try:
            if rlt:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": today})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
            else:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": pre_trade_date})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)

#小市值前100名，分别取涨幅和成交量前100名
    def get_small_mv_100_pool(self):
        SQL_COMM = """ select * from 
(select chengjiao.symbol,chengjiao.trade_date,chengjiao.name ,chengjiao.zhangdiefu,shizhi.industry,shizhi.float_value,float_share, chengjiao.amount from 
(
select symbol,trade_date,name,close,zhangdiefu,amount as amount,amplitude,turnover_rate from ak_stock_daily_qfq_em where trade_date= current_date()  ) chengjiao
join 
(select symbol,name,float_value as float_value,float_share,industry from ak_stock_list where float_value <3500000000  and symbol not in (select symbol from black_stock_list)
)shizhi 
on chengjiao.symbol=shizhi.symbol 
order by zhangdiefu desc limit 100 
) AAA 

UNION 
select * from (
select chengjiao.symbol,chengjiao.trade_date,chengjiao.name ,chengjiao.zhangdiefu,shizhi.industry,shizhi.float_value,float_share, chengjiao.amount from 
(
select symbol,trade_date,name,close,zhangdiefu,amount as amount,amplitude,turnover_rate from ak_stock_daily_qfq_em where trade_date= current_date()  ) chengjiao
join 
(select symbol,name,float_value as float_value,float_share,industry from ak_stock_list where float_value <3500000000 and symbol not in (select symbol from black_stock_list)
)shizhi 
on chengjiao.symbol=shizhi.symbol 
order by amount desc limit 100 ) bbb

            """
        dbengine = self.dbengine

        try:

            df = pd.read_sql(text(SQL_COMM), dbengine )
            stock_pool = df['symbol'].tolist()
            return stock_pool, df

        except Exception as e:
            print('读取数据出错', e)


    def get_zuiqiang100_up_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()
        SQL_COMM = """select chengjiao.symbol,chengjiao.trade_date,chengjiao.name ,chengjiao.zhangdiefu,close,shizhi.industry,shizhi.float_value, chengjiao.amount from 
(
select symbol,trade_date,name,close,zhangdiefu,amount/100000000 as amount,amplitude,turnover_rate from ak_stock_daily_qfq_em where trade_date= current_date() order by amount desc limit 100 ) chengjiao
join 
(select symbol,name,float_value/100000000 as float_value,industry from ak_stock_list where float_value >10000000000 and symbol not in (select symbol from black_stock_list)
)shizhi 
on chengjiao.symbol=shizhi.symbol 
order by zhangdiefu desc limit 70 ;"""
               # 每日涨幅大于0的 ，排序取前100名，然后过滤， 而且市值再大于50亿的

        dbengine = self.dbengine

        try:
            if rlt:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": today})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
            else:
                df = pd.read_sql(text(SQL_COMM), dbengine, params={"pre_trade_date": pre_trade_date})
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)

    def get_em_hangye_pool(self):
        sql_comm="SELECT em_hy_nm as name,em_hy_code as symbol FROM ak_hangye_list_em"
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            hangye_list = df['symbol'].tolist()
            return hangye_list,df
        except Exception as e:
            print('读取数据出错', e)

    def get_em_gainain_pool(self):
        sql_comm="SELECT em_gn_nm as name,em_gn_code as symbol FROM ak_ganian_list_em"
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            hangye_list = df['symbol'].tolist()
            return hangye_list,df
        except Exception as e:
            print('读取数据出错', e)

    def get_myetf_pool(self):
        sql_comm="SELECT symbol,name FROM my_index_fund_list"
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            hangye_list = df['symbol'].tolist()
            return hangye_list,df
        except Exception as e:
            print('读取数据出错', e)
    
    def get_myetf_daily_someday(self,symbol,start_date=(datetime.today()-timedelta(days=120)).strftime('%Y%m%d'),end_date='20500101'):
        dbengine = self.dbengine
        sql_comm = "select symbol,trade_date,name,open,high,low,close,volume,amount,zhangdiefu from my_index_fund_daily_em where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index = df['trade_date']
            return df
        except Exception as e:
            print('读取数据出错', e)

    def get_em_hangye_daily_someday(self,symbol,start_date='',end_date=''):
        if start_date=='':
            start_date='20220402'
        else:
            start_date=start_date
        if end_date=='':
            end_date=datetime.today().strftime('%Y-%m-%d')
        else:
            end_date=end_date
        dbengine = self.dbengine
        sql_comm = "select symbol,trade_date,name,open,high,low,close,volume,amount,zhangdiefu from ak_hangye_daily_em where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)

    def get_fund_pool(self):
        sql_comm="select symbol,name,float_value from ak_fund_list_def"
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            hangye_list = df['symbol'].tolist()
            return hangye_list, df
        except Exception as e:
            print('读取数据出错', e)


    def get_em_gainian_daily_someday(self,symbol,start_date='',end_date=''):
        if start_date=='':
            start_date='20220402'
        else:
            start_date=start_date
        if end_date=='':
            end_date=datetime.now().strftime('%Y%m%d')
        else:
            end_date=end_date
        dbengine = self.dbengine
        sql_comm = "select symbol,trade_date,name,open,high,low,close,volume,amount,zhangdiefu from em_gn_daily where symbol = :symbol and trade_date>:start_date and trade_date <= :end_date order by trade_date asc"
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)

    def index_300_daily_all(self):
        dbengine = self.dbengine
        sql_comm = "select * from ak_index_daily_300_em order by trade_date asc"
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)
            return exit(1)

    def index_300_daily_someday(self,start_date='',end_date=''):
        if start_date == '':
            start_date = '20220202'
        else:
            start_date = start_date
        if end_date == '':
            end_date = datetime.now().strftime('%Y%m%d')
        else:
            end_date = end_date
        dbengine = self.dbengine
        sql_comm = "select * from ak_index_daily_300_em where  trade_date>:start_date and trade_date <= :end_date"
        try:
            df = pd.read_sql(text(sql_comm), dbengine,
                             params={"start_date": start_date, 'end_date': end_date})
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)





    def index_001_daily_all(self):
        dbengine = self.dbengine
        sql_comm = "select * from ak_index_daily_001_em order by trade_date asc"
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine)
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)
            return exit(1)

    def index_001_daily_someday(self, start_date='', end_date=''):
        if start_date == '':
            start_date = '20220202'
        else:
            start_date = start_date
        if end_date == '':
            end_date = datetime.now().strftime('%Y%m%d')
        else:
            end_date = end_date
        dbengine = self.dbengine
        sql_comm = "select * from ak_index_daily_001_em where  trade_date>:start_date and trade_date <= :end_date"
        try:
            df = pd.read_sql(text(sql_comm), dbengine,
                             params={"start_date": start_date, 'end_date': end_date})
            df.index = df['trade_date']
            return df, end_date
        except Exception as e:
            print('读取数据出错', e)


    def index_300_with_code_daily_all(self,symbol):
        #沪深300指数，配合个股的交易日历， 如果个股交易，中间有停盘，不要沪深300指数， 这样，就可以同步时间长度
        sql_comm="select * from ak_index_daily_300_em where trade_date in (select trade_date from ak_stock_daily_qfq_em where symbol= :symbol)"
        dbengine = self.dbengine
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
            df.index=df['trade_date']
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)

    def index_300_with_code_someday(self,symbol,start_date="",end_date=""):
        if start_date == '':
            start_date = '20220202'
        else:
            start_date = start_date
        if end_date == '':
            end_date = datetime.now().strftime('%Y%m%d')
        else:
            end_date = end_date
        #沪深300指数，配合个股的交易日历， 如果个股交易，中间有停盘，不要沪深300指数， 这样，就可以同步时间长度
        sql_comm="select * from ak_index_daily_300_em where trade_date in (select trade_date from ak_stock_daily_qfq_em where symbol= :symbol and trade_date>:start_date and trade_date <= :end_date)"
        dbengine = self.dbengine
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index=df['trade_date']
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)


    def index_001_with_code_daily_all(self,symbol):
        #沪深300指数，配合个股的交易日历， 如果个股交易，中间有停盘，不要沪深300指数， 这样，就可以同步时间长度
        sql_comm="select * from ak_index_daily_001_em where trade_date in (select trade_date from ak_stock_daily_qfq_em where symbol= :symbol)"
        dbengine = self.dbengine
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
            df.index=df['trade_date']
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)


    def index_001_with_code_weekly_all(self,symbol):
        #沪深300指数，配合个股的交易日历， 如果个股交易，中间有停盘，不要沪深300指数， 这样，就可以同步时间长度
        sql_comm="select * from ak_index_weekly_001_em where trade_date in (select trade_date from ak_stock_weekly_qfq_em where symbol= :symbol)"
        dbengine = self.dbengine
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol})
            df.index=df['trade_date']
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)

    def index_001_with_code_someday(self,symbol,start_date="",end_date=""):
        if start_date == '':
            start_date = '20220202'
        else:
            start_date = start_date
        if end_date == '':
            end_date = datetime.now().strftime('%Y%m%d')
        else:
            end_date = end_date
        #沪深300指数，配合个股的交易日历， 如果个股交易，中间有停盘，不要沪深300指数， 这样，就可以同步时间长度
        sql_comm="select * from ak_index_daily_001_em where trade_date in (select trade_date from ak_stock_daily_qfq_em where symbol= :symbol and trade_date>:start_date and trade_date <= :end_date)"
        dbengine = self.dbengine
        end_date = datetime.now().strftime('%Y%m%d')
        try:
            df = pd.read_sql(text(sql_comm), dbengine, params={"symbol": symbol,"start_date":start_date,'end_date':end_date})
            df.index=df['trade_date']
            return df,end_date
        except Exception as e:
            print('读取数据出错', e)
            return  exit(1)





    # 获取当前持仓股票池，用于监控卖点
    def get_postion_stock_pool(self):
        today, rlt, pre_trade_date = self.is_trade_day_today()
        SQL_COMM = """
select pos.symbol,pos.name,quantity,leftover,frozen,amount,profit,profit_ratio,cost,lst.industry,lst.float_share,lst.float_value from quant_result.acc_pos pos
left join 
quant.ak_stock_list lst
on pos.symbol=lst.symbol where leftover >0 
        """
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(SQL_COMM), dbengine)
            stock_pool = df['symbol'].tolist()
            return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)

    # 获取qmt 的etf 持仓池
    def get_qmt_postion_etf_pool(self):
        # today, rlt, pre_trade_date = self.is_trade_day_today()

        SQL_COMM = """
select pos.stock_code,SUBSTR(stock_code,1,6) as symbol,pos.stock_name as name,yesterday_volume,can_use_volume,frozen_volume,yingkuilv,strategy_name,buy_date from quant_result.acc_qmt_position_etf pos
where yesterday_volume >0 and can_use_volume >0  
        """
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(SQL_COMM), dbengine)
            stock_pool = df['stock_code'].tolist()
            return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)


    def get_qmt_postion_stock_pool(self):
        # today, rlt, pre_trade_date = self.is_trade_day_today()

        SQL_COMM = """
select pos.stock_code,SUBSTR(stock_code,1,6) as symbol,pos.stock_name as name,yesterday_volume,can_use_volume,frozen_volume,yingkuilv,strategy_name,buy_date from quant_result.acc_qmt_position_stock pos
where yesterday_volume >0 and can_use_volume >0  
        """
        dbengine = self.dbengine
        try:
            df = pd.read_sql(text(SQL_COMM), dbengine)
            stock_pool = df['symbol'].tolist()
            return stock_pool, df
        except Exception as e:
            print('读取数据出错', e)

    def get_qmt_postion_pool(self):
            # today, rlt, pre_trade_date = self.is_trade_day_today()

            SQL_COMM = """
    select pos.stock_code,SUBSTR(stock_code,1,6) as symbol,pos.stock_name as name,yesterday_volume,can_use_volume,frozen_volume from quant_result.acc_qmt_position_stock pos
    where yesterday_volume >0 and can_use_volume >0  
            """
            dbengine = self.dbengine
            try:
                df = pd.read_sql(text(SQL_COMM), dbengine)
                stock_pool = df['symbol'].tolist()
                return stock_pool, df
            except Exception as e:
                print('读取数据出错', e)
    def get_stock_signal_moni(self,strategy_name=''):
            # today, rlt, pre_trade_date = self.is_trade_day_today()
            if strategy_name == '':

                SQL_COMM = """select * from stock_signal_moni  where 1=1 """.format(strategy_name)
            else:
                SQL_COMM = """select * from stock_signal_moni  where strategy_name={} ;""".format(strategy_name)
            try:
                df = pd.read_sql(text(SQL_COMM), self.dbengine)
                df['trade_date']=df['trade_date'].astype(str)
                return  df
            except Exception as e:
                print('读取数据出错', e)

    def get_stock_trade_record(self,strategy_name=''):
        """
        获取股票交易记录,主要取前一天的交易记录，，往前取两天，
        :param strategy_name:
        :return:
        """
        today, rlt, pre_trade_date = self.is_trade_day_today()
        if rlt == False:
                return pd.DataFrame()
        else:
            try:
                if strategy_name == '':
                    SQL_COMM = """select * from stock_signal_trade_prod  where 1=1 """.format(strategy_name)
                    df = pd.read_sql(text(SQL_COMM), self.dbengine)
                    df['trade_date']=df['trade_date'].astype(str)
                    return  df
                else:
                    # SQL_COMM = """select * from stock_trade_record  where strategy_name={} and trade_date={} ;""".format(strategy_name,pre_trade_date)
                    SQL_COMM= """select * from stock_signal_trade_prod where trade_date<:today and trade_date>=(select pretrade_date from trade_cal_ak_source where is_open=1 and nexttrade_date=:pre_trade_date)"""
                    params={'pre_trade_date':pre_trade_date,'today':today}
                    df = pd.read_sql(text(SQL_COMM), self.dbengine,params=params)
                    df['trade_date']=df['trade_date'].astype(str)
                    return  df


            except Exception as e:
                    print('读取数据出错', e)
# test usage
if __name__ == '__main__':
    # stock_individual_info_em_df = ak.stock_individual_info_em()
    # print(stock_individual_info_em_df)
    # stock_info_a_code_name= ak.stock_info_a_code_name()      # 股票列表 要进入数据库才行，有点慢
    print("stock_info_a_code_name")

